﻿namespace com.vs.DataAccess
{
    using com.vs.General;
    using System;
    using System.Collections;
    using System.Data;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading;

    public class DataHelper : IDisposable
    {
        public IDbCommand _command;
        public string _connString = null;
        private IDbFactory _factory = null;
        public const int _NO_OF_RETRIES = 3;
        public Session _session;
        public IDataParameter[] _sharedParameters;
        public const int _SLEEP_TIME = 100;
        public bool _traceSQL;

        public DataHelper(Session session)
        {
            this._session = session;
            this._sharedParameters = null;
            if (session == null)
            {
                throw new Exception("No session.");
            }
            this._connString = session.ConnectionString;
            this._factory = new SqlFactory();
            this._command = this._factory.CreateConnection(this._connString).CreateCommand();
            this._command.CommandTimeout = 600;
            if (this._command.Connection.State != ConnectionState.Open)
            {
                this._command.Connection.Open();
            }
        }

        public IDataParameter BuildOutputParameter(string name, DbType type, int size)
        {
            return this._factory.CreateOutputParameter(name, type, size);
        }

        public static IDataParameter BuildOutputParameter(Session session, string name, DbType type, int size)
        {
            IDbFactory factory = null;
            if (session == null)
            {
                throw new Exception("No session.");
            }
            factory = new SqlFactory();
            return factory.CreateOutputParameter(name, type, size);
        }

        public IDataParameter BuildParameter(string name, DateTime val)
        {
            IDataParameter parameter = this._factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public IDataParameter BuildParameter(string name, decimal val)
        {
            IDataParameter parameter = this._factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public IDataParameter BuildParameter(string name, double val)
        {
            IDataParameter parameter = this._factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public IDataParameter BuildParameter(string name, int val)
        {
            IDataParameter parameter = this._factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public IDataParameter BuildParameter(string name, long val)
        {
            IDataParameter parameter = this._factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public IDataParameter BuildParameter(string name, string val)
        {
            IDataParameter parameter = this._factory.CreateParameter(name);
            parameter.DbType = DbType.String;
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public static IDataParameter BuildParameter(Session session, string name, DateTime val)
        {
            IDbFactory factory = null;
            if (session == null)
            {
                throw new Exception("No session.");
            }
            factory = new SqlFactory();
            IDataParameter parameter = factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public static IDataParameter BuildParameter(Session session, string name, decimal val)
        {
            IDbFactory factory = null;
            if (session == null)
            {
                throw new Exception("No session.");
            }
            factory = new SqlFactory();
            IDataParameter parameter = factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public static IDataParameter BuildParameter(Session session, string name, double val)
        {
            IDbFactory factory = null;
            if (session == null)
            {
                throw new Exception("No session.");
            }
            factory = new SqlFactory();
            IDataParameter parameter = factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public static IDataParameter BuildParameter(Session session, string name, int val)
        {
            IDbFactory factory = null;
            if (session == null)
            {
                throw new Exception("No session.");
            }
            factory = new SqlFactory();
            IDataParameter parameter = factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public static IDataParameter BuildParameter(Session session, string name, long val)
        {
            IDbFactory factory = null;
            if (session == null)
            {
                throw new Exception("No session.");
            }
            factory = new SqlFactory();
            IDataParameter parameter = factory.CreateParameter(name);
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public static IDataParameter BuildParameter(Session session, string name, string val)
        {
            IDbFactory factory = null;
            if (session == null)
            {
                throw new Exception("No session.");
            }
            factory = new SqlFactory();
            IDataParameter parameter = factory.CreateParameter(name);
            parameter.DbType = DbType.String;
            parameter.Value = MaskParameterValue(val);
            return parameter;
        }

        public static string BuildTraceMessage(string sql, params IDataParameter[] parameters)
        {
            StringBuilder builder = new StringBuilder(sql);
            if (parameters != null)
            {
                foreach (IDataParameter parameter in parameters)
                {
                    builder.Append(";").Append(parameter.ParameterName).Append("=").Append(parameter.Value);
                }
            }
            return builder.ToString();
        }

        public void Commit()
        {
            if (this._command.Transaction != null)
            {
                this._command.Transaction.Commit();
                this._command.Transaction = null;
            }
        }

        public static string DeriveParameterName(IDataParameter param)
        {
            return DeriveParameterName(param.ParameterName);
        }

        public static string DeriveParameterName(string param)
        {
            return param.Replace("@", "").Replace("_", "").ToUpper();
        }

        public IDataParameter[] DiscoverParameters(CommandType cmdType, string cmdText, bool includeReturnValueParam, object[] parameters)
        {
            IDataParameter[] array = null;
            if (cmdType == CommandType.Text)
            {
                Hashtable hashtable = new Hashtable();
                MatchCollection matchs = new Regex(@"@[a-z]\w+", RegexOptions.IgnoreCase).Matches(cmdText);
                foreach (Match match in matchs)
                {
                    if (!hashtable.ContainsKey(match.Value))
                    {
                        IDataParameter parameter = this._factory.CreateParameter();
                        parameter.ParameterName = match.Value;
                        hashtable.Add(match.Value, parameter);
                    }
                }
                array = new IDataParameter[hashtable.Count];
                hashtable.Values.CopyTo(array, 0);
            }
            else if (cmdType == CommandType.StoredProcedure)
            {
                array = DataHelperParameterCache.GetSpParameterSet(this._session, cmdText, includeReturnValueParam);
            }
            int index = 0;
            for (int i = 0; i < array.Length; i++)
            {
                if ((array[i].Direction == ParameterDirection.Input) || (array[i].Direction == ParameterDirection.InputOutput))
                {
                    array[i].Value = ((parameters != null) && (parameters.Length > index)) ? parameters[index] : DBNull.Value;
                    index++;
                }
            }
            return array;
        }

        public void Dispose()
        {
            this.Rollback();
            if (this._command.Connection != null)
            {
                this._command.Connection.Close();
                this._command.Connection.Dispose();
            }
            this._command.Dispose();
        }

        public bool GetBool(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return StringManager.Equals(this.GetString(cmdType, cmdText, parameters), "Y", true);
        }

        public DataSet GetDataSet(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            DataSet set2;
            try
            {
                int num;
                if (StringManager.IsEmpty(cmdText))
                {
                    return null;
                }
                if ((cmdType == CommandType.StoredProcedure) && !ArrayManager.IsEmpty(parameters))
                {
                    string[] strArray = new string[parameters.Length];
                    for (num = 0; num < parameters.Length; num++)
                    {
                        if (((parameters[num] != null) && !Convert.IsDBNull(parameters[num].Value)) && (parameters[num].Value != null))
                        {
                            strArray[num] = parameters[num].Value.ToString();
                        }
                    }
                    parameters = this.DiscoverParameters(CommandType.StoredProcedure, cmdText, false, strArray);
                }
                this.PrepareCommand(cmdType, ref cmdText, parameters);
                DataSet dataSet = new DataSet();
                for (num = 0; num < 3; num++)
                {
                    try
                    {
                        this.DBFactory.CreateDataAdapter(this._command).Fill(dataSet);
                        break;
                    }
                    catch (Exception exception)
                    {
                        if (!this._factory.IsOperationToBeRetried(exception) || (this._command.Transaction != null))
                        {
                            throw exception;
                        }
                        if (num == 2)
                        {
                            throw exception;
                        }
                        Thread.Sleep(100);
                    }
                }
                set2 = dataSet;
            }
            catch (Exception exception2)
            {
                throw exception2;
            }
            return set2;
        }

        public int GetInt(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return Convert.ToInt32(this.GetScalarHelper(cmdType, cmdText, parameters));
        }

        public long GetLong(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return Convert.ToInt64(this.GetScalarHelper(cmdType, cmdText, parameters));
        }

        public IDataReader GetReader(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            IDataReader reader = null;
            IDataReader reader2;
            try
            {
                if (StringManager.IsEmpty(cmdText))
                {
                    return null;
                }
                if (cmdText.IndexOf(",,") >= 0)
                {
                    cmdText = cmdText.Replace(",,", ",null,").Replace(",,", ",null,");
                }
                this.PrepareCommand(cmdType, ref cmdText, parameters);
                for (int i = 0; i < 3; i++)
                {
                    try
                    {
                        reader = this._command.ExecuteReader();
                        break;
                    }
                    catch (Exception exception)
                    {
                        if (!this._factory.IsOperationToBeRetried(exception) || (this._command.Transaction != null))
                        {
                            throw exception;
                        }
                        if (i == 2)
                        {
                            throw exception;
                        }
                        Thread.Sleep(100);
                    }
                }
                reader2 = reader;
            }
            catch (Exception exception2)
            {
                throw exception2;
            }
            return reader2;
        }

        public IDataReader GetReaderWithValues(CommandType cmdType, string cmdText, params object[] paramValues)
        {
            return this.GetReaderWithValues(cmdType, cmdText, this._session != null, paramValues);
        }

        public IDataReader GetReaderWithValues(CommandType cmdType, string cmdText, bool includeReturnValueParam, params object[] paramValues)
        {
            if (StringManager.IsEmpty(cmdText))
            {
                return null;
            }
            return this.GetReader(cmdType, cmdText, this.DiscoverParameters(cmdType, cmdText, includeReturnValueParam, paramValues));
        }

        public DataRow GetRow(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            DataTable table = this.GetTable(cmdType, cmdText, parameters);
            if ((table == null) || (table.Rows.Count == 0))
            {
                return null;
            }
            return table.Rows[0];
        }

        public decimal GetScalar(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return Convert.ToDecimal(this.GetScalarHelper(cmdType, cmdText, parameters));
        }

        public object GetScalarHelper(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            object obj3;
            try
            {
                if (StringManager.IsEmpty(cmdText))
                {
                    return null;
                }
                if (cmdText.IndexOf(",,") >= 0)
                {
                    cmdText = cmdText.Replace(",,", ",null,").Replace(",,", ",null,");
                }
                this.PrepareCommand(cmdType, ref cmdText, parameters);
                object obj2 = null;
                for (int i = 0; i < 3; i++)
                {
                    try
                    {
                        obj2 = this._command.ExecuteScalar();
                        break;
                    }
                    catch (Exception exception)
                    {
                        if (!this._factory.IsOperationToBeRetried(exception) || (this._command.Transaction != null))
                        {
                            throw exception;
                        }
                        if (i == 2)
                        {
                            throw exception;
                        }
                        Thread.Sleep(100);
                    }
                }
                obj3 = (obj2 == DBNull.Value) ? null : obj2;
            }
            catch (Exception exception2)
            {
                throw exception2;
            }
            return obj3;
        }

        public object GetScalarObject(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return this.GetScalarHelper(cmdType, cmdText, parameters);
        }

        public decimal GetScalarWithValues(CommandType cmdType, string cmdText, params object[] paramValues)
        {
            return this.GetScalarWithValues(cmdType, cmdText, paramValues);
        }

        public decimal GetScalarWithValues(CommandType cmdType, string cmdText, bool includeReturnValueParam, params object[] paramValues)
        {
            if (StringManager.IsEmpty(cmdText))
            {
                return 0M;
            }
            return this.GetScalar(cmdType, cmdText, this.DiscoverParameters(cmdType, cmdText, includeReturnValueParam, paramValues));
        }

        public string GetString(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            return Convert.ToString(this.GetScalarHelper(cmdType, cmdText, parameters));
        }

        public DataTable GetTable(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            DataSet set = this.GetDataSet(cmdType, cmdText, parameters);
            if ((set == null) || (set.Tables.Count == 0))
            {
                return null;
            }
            return set.Tables[0];
        }

        public int Insert(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            int num3;
            try
            {
                if (StringManager.IsEmpty(cmdText))
                {
                    return -1;
                }
                if (cmdText.IndexOf(",,") >= 0)
                {
                    cmdText = cmdText.Replace(",,", ",null,").Replace(",,", ",null,");
                }
                this.PrepareCommand(cmdType, ref cmdText, parameters);
                int num = 0;
                for (int i = 0; i < 3; i++)
                {
                    try
                    {
                        num = this._command.ExecuteNonQuery();
                        break;
                    }
                    catch (Exception exception)
                    {
                        if (!this._factory.IsOperationToBeRetried(exception) || (this._command.Transaction != null))
                        {
                            throw exception;
                        }
                        if (i == 2)
                        {
                            throw exception;
                        }
                        Thread.Sleep(100);
                    }
                }
                num3 = num;
            }
            catch (Exception exception2)
            {
                throw exception2;
            }
            return num3;
        }

        public int Insert(CommandType cmdType, string cmdText, string key, params IDataParameter[] parameters)
        {
            int num3;
            try
            {
                if (StringManager.IsEmpty(cmdText))
                {
                    return -1;
                }
                if (cmdText.IndexOf(",,") >= 0)
                {
                    cmdText = cmdText.Replace(",,", ",null,").Replace(",,", ",null,");
                }
                cmdText = this._factory.PrepareInsert(cmdText);
                this.PrepareCommand(cmdType, ref cmdText, parameters);
                int num = 0;
                for (int i = 0; i < 3; i++)
                {
                    try
                    {
                        num = this.DBFactory.ExecuteInsert(this._command, key);
                        break;
                    }
                    catch (Exception exception)
                    {
                        if (!this._factory.IsOperationToBeRetried(exception) || (this._command.Transaction != null))
                        {
                            throw exception;
                        }
                        if (i == 2)
                        {
                            throw exception;
                        }
                        Thread.Sleep(100);
                    }
                }
                num3 = num;
            }
            catch (Exception exception2)
            {
                throw exception2;
            }
            return num3;
        }

        public static bool IsDatabaseAvailable(string connectionString)
        {
            IDbConnection connection = null;
            bool flag;
            try
            {
                if (!StringManager.IsEmpty(connectionString))
                {
                    IDbFactory factory = new SqlFactory();
                    connection = factory.CreateConnection(connectionString, 5);
                    try
                    {
                        connection.Open();
                    }
                    catch
                    {
                        return false;
                    }
                    return true;
                }
                flag = false;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return flag;
        }

        public bool IsDBAvailable()
        {
            IDbConnection connection = null;
            bool flag;
            try
            {
                connection = this._factory.CreateConnection(this._session.ConnectionString, 5);
                try
                {
                    connection.Open();
                }
                catch
                {
                    return false;
                }
                flag = true;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return flag;
        }

        public static object MaskParameterValue(DateTime val)
        {
            if (val == DateTime.MinValue)
            {
                return DBNull.Value;
            }
            return val;
        }

        public static object MaskParameterValue(decimal val)
        {
            if (val == -79228162514264337593543950335M)
            {
                return DBNull.Value;
            }
            if (val > 10000000000000000000000000000M)
            {
                return 10000000000000000000000000000M;
            }
            return val;
        }

        public static object MaskParameterValue(double val)
        {
            if (val == -2147483648.0)
            {
                return DBNull.Value;
            }
            if (val > 1E+28)
            {
                return 1E+28;
            }
            return val;
        }

        public static object MaskParameterValue(int val)
        {
            if (val == -2147483648)
            {
                return DBNull.Value;
            }
            if (val > 0x3b9ac9ff)
            {
                return 0x3b9ac9ff;
            }
            return val;
        }

        public static object MaskParameterValue(long val)
        {
            if (val == -9223372036854775808L)
            {
                return DBNull.Value;
            }
            if (val > 0x5af3107a3fffL)
            {
                return 0x5af3107a3fffL;
            }
            return val;
        }

        public static object MaskParameterValue(string val)
        {
            if ((val == null) || (val == ""))
            {
                return DBNull.Value;
            }
            return val;
        }

        public void PrepareCommand(CommandType cmdType, ref string cmdText, IDataParameter[] parameters)
        {
            this._command.CommandType = cmdType;
            cmdText = (cmdType == CommandType.Text) ? this._factory.ProcessSqlText(cmdText) : cmdText;
            this._command.CommandText = cmdText;
            if ((parameters == null) || (parameters.Length == 0))
            {
                parameters = this.SharedParameters;
            }
            this._command.Parameters.Clear();
            if (parameters != null)
            {
                foreach (IDataParameter parameter in parameters)
                {
                    if ((parameter.Direction != ParameterDirection.Output) && (parameter.Direction != ParameterDirection.ReturnValue))
                    {
                        parameter.ParameterName = this._factory.GetParameterName(parameter.ParameterName);
                        if (parameter.Value == null)
                        {
                            parameter.Value = DBNull.Value;
                        }
                    }
                    if ((cmdType == CommandType.StoredProcedure) && (parameter.ParameterName[0] == ':'))
                    {
                        parameter.ParameterName = parameter.ParameterName.Substring(1, parameter.ParameterName.Length - 1);
                    }
                    this._command.Parameters.Add(parameter);
                }
            }
        }

        public void Rollback()
        {
            if (this._command.Transaction != null)
            {
                this._command.Transaction.Rollback();
                this._command.Transaction = null;
            }
        }

        public static string RtrvStoredProcCall(Session sess, string stProcName, string[] stParmArray)
        {
            string str;
            int length = 0;
            StringBuilder builder = null;
            try
            {
                builder = new StringBuilder();
                builder.Append("exec ").Append(stProcName).Append(" ");
                if (stParmArray != null)
                {
                    length = stParmArray.Length;
                    for (int i = 0; i < length; i++)
                    {
                        if ((stParmArray[i] != null) && stParmArray[i].Equals("''"))
                        {
                            stParmArray[i] = null;
                        }
                        builder.Append(stParmArray[i]).Append(",");
                    }
                    builder.Length--;
                }
                str = builder.ToString();
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                builder = null;
                stProcName = null;
                stParmArray = null;
            }
            return str;
        }

        public void StartTransaction()
        {
            if (this._command.Transaction != null)
            {
                throw new Exception("Pending Transaction ?cannot start a new transaction");
            }
            this._command.Transaction = this._command.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
        }

        public void StartTransaction(IsolationLevel isolationLevel)
        {
            if (this._command.Transaction != null)
            {
                throw new Exception("Pending Transaction ?cannot start a new transaction");
            }
            this._command.Transaction = this._command.Connection.BeginTransaction(isolationLevel);
        }

        public int Update(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            int num3;
            try
            {
                if (StringManager.IsEmpty(cmdText))
                {
                    return -1;
                }
                if (cmdText.IndexOf(",,") >= 0)
                {
                    cmdText = cmdText.Replace(",,", ",null,").Replace(",,", ",null,");
                }
                this.PrepareCommand(cmdType, ref cmdText, parameters);
                int num = 0;
                for (int i = 0; i < 3; i++)
                {
                    try
                    {
                        num = this._command.ExecuteNonQuery();
                        break;
                    }
                    catch (Exception exception)
                    {
                        if (!this._factory.IsOperationToBeRetried(exception) || (this._command.Transaction != null))
                        {
                            throw;
                        }
                        if (i == 2)
                        {
                            throw exception;
                        }
                        Thread.Sleep(100);
                    }
                }
                num3 = num;
            }
            catch (Exception exception2)
            {
                throw exception2;
            }
            return num3;
        }

        public int UpdateWithValues(CommandType cmdType, string cmdText, params object[] paramValues)
        {
            return this.UpdateWithValues(cmdType, cmdText, this._session != null, paramValues);
        }

        public int UpdateWithValues(CommandType cmdType, string cmdText, bool includeReturnValueParam, params object[] paramValues)
        {
            if (StringManager.IsEmpty(cmdText))
            {
                return 0;
            }
            return this.Update(cmdType, cmdText, this.DiscoverParameters(cmdType, cmdText, includeReturnValueParam, paramValues));
        }

        internal IDbFactory DBFactory
        {
            get
            {
                return this._factory;
            }
        }

        public bool IsSQLTracingEnabled
        {
            get
            {
                return this._traceSQL;
            }
        }

        public IDataParameter[] SharedParameters
        {
            get
            {
                return this._sharedParameters;
            }
            set
            {
                this._sharedParameters = value;
            }
        }

        public int Timeout
        {
            get
            {
                return ((this._command == null) ? -1 : this._command.CommandTimeout);
            }
            set
            {
                if (this._command != null)
                {
                    this._command.CommandTimeout = value;
                }
            }
        }
    }
}

